******SET YOUR DIRECTORY to ...\replication



///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.11
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr

*Create variables
gen vc_pc=totnpcc_cc_offenses_vc*1000
gen vc_ncc_pc=totnpcc_ncc_offenses_vc*1000
gen propemp=cemp/emp
gen trend=year-1960
gen wateryear=prop_area_water*trend
gen slopeyear=prop_area_slope*trend
gen l10perc_cc=L10.perc_cc

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Label variables
label var vc_pc "Violent crime CC"
label var inter_badtetra "High LB x Lead"
label var vc_ncc_pc "Violent crime NCC"
label var cpctfst "Sh. foreign pop. CC"
label var perc_cc "Sh. Pop CC"
label var l10perc_cc "Sh. Pop CC (10 years lag)"

***OLS
reghdfe perc_cc vc_pc, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, OLS, F, .) replace
***OLS, LATE
reghdfe perc_cc vc_pc if cdivcode==4 | cdivcode==6 | cdivcode==8 | cdivcode==9, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, No east, Estimation, OLS, F, .) 
***OLS, LATE, omitted variables, crime NCC
reghdfe perc_cc vc_pc vc_ncc_pc if cdivcode==4 | cdivcode==6 | cdivcode==8 | cdivcode==9, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, No east, Estimation, OLS, F, .) 
***OLS, LATE, omitted variables, crime NCC & % foreign in CC
reghdfe perc_cc vc_pc vc_ncc_pc cpctfst if cdivcode==4 | cdivcode==6 | cdivcode==8 | cdivcode==9, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, No east, Estimation, OLS, F, .) 
***Reverse causality, OLS
reghdfe vc_pc perc_cc, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Violent CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, OLS, F, .) 
***Reverse causality, IV
ivreghdfe vc_pc (perc_cc=wateryear slopeyear), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Violent CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, IV, F, `ff') 
***Reverse causality, control past suburbanization
reghdfe perc_cc vc_pc l10perc_cc, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias, tex(frag) nocon noni  nonotes label ctitle("Violent CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, OLS, F, .) 



///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.12
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen bad_soil=1-good_soil
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr

*Create variables
gen vc_pc=totnpcc_cc_offenses_vc*1000
gen vc_ncc_pc=totnpcc_ncc_offenses_vc*1000
gen pctover25=over25/pop_msa

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Label variables
label var vc_pc "Violent crime CC"
label var inter_badtetra "High LB x Lead"
label var vc_ncc_pc "Violent crime NCC"
label var pctblk "Sh. blacks MSA"
label var income "Median family income MSA"
label var rent "Median gross rent MSA"
label var value "Median housing value MSA"
label var pctover25 "Sh. over 25 MSA"
label var pct65up "Sh. over 65 MSA"


***OLS
reghdfe perc_cc vc_pc, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 vc_pc using table_bias_rsq, tex(frag) nocon noni nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, OLS, F, ., Bias-adjusted t.e. sol. 1, ., Bias-adjusted t.e. sol. 2, ., Degree selection, .) replace
***OLS, only FE
reghdfe perc_cc, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
outreg2 using table_bias_rsq, tex(frag) nocon noni nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Sample, All, Estimation, OLS, F, ., Bias-adjusted t.e. sol. 1, ., Bias-adjusted t.e. sol. 2, ., Degree selection, .)
***OLS, only MSA FE
reghdfe perc_cc, abs(fipsplace_00) cluster(cdivcodeyear)
outreg2 using table_bias_rsq, tex(frag) nocon noni nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, NO, C. div x Year, NO, Sample, All, Estimation, OLS, F, ., Bias-adjusted t.e. sol. 1, ., Bias-adjusted t.e. sol. 2, ., Degree selection, .)
***OLS, cross-sectional
reg perc_cc vc_pc if year==1990, rob
outreg2 vc_pc using table_bias_rsq, tex(frag) nocon noni nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, NO, Year FE, NO, C. div x Year, NO, Sample, 1990, Estimation, OLS, F, ., Bias-adjusted t.e. sol. 1, ., Bias-adjusted t.e. sol. 2, ., Degree selection, .)
***IV, cross-sectional
ivreg2 perc_cc (vc_pc=bad_soil) if year==1990, rob
local iv_eff=_b[vc_pc]
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_bias_rsq, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, NO, Year FE, NO, C. div x Year, NO, Sample, 1990, Estimation, IV, F, `ff', Bias-adjusted t.e. sol. 1, ., Bias-adjusted t.e. sol. 2, ., Degree selection, .)
***OLS, cross-sectional with controls
reg perc_cc vc_pc vc_ncc_pc pctblk income rent value pctover25 pct65up if year==1990, rob
psacalc delta vc_pc, beta(`iv_eff')
di `iv_eff'
local delta= string(round(r(delta),.001) , "%9.3f")
psacalc beta vc_pc
local beta_1= string(round(r(altsol1),.001) , "%9.3f")
local beta_2= string(round(r(beta),.001) , "%9.3f")
outreg2 vc_pc vc_ncc_pc pctblk income rent value pctover25 pct65up using table_bias_rsq, tex(frag) nocon noni nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, NO, Year FE, NO, C. div x Year, NO, Sample, 1990, Estimation, OLS, F, ., Bias-adjusted t.e. sol. 1, `beta_1', Bias-adjusted t.e. sol. 2, `beta_2', Degree selection, `delta')


///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.13
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr

*Create variables
gen vc_pc=totnpcc_cc_offenses_vc*1000
g lnpop_cc = ln(tot_cc_oripop_corr)
g crime_pc = (totnpcc_cc_offenses_vc + totnpcc_cc_offenses_17c + totnpcc_cc_offenses_21c + totnpcc_cc_offenses_22c)*1000

*Cluster variables
egen cdivcodeyear=group(cdivcode year)

*Label variables
label var vc_pc "Violent crime CC"
label var inter_badtetra "High LB x Lead"
label var crime_pc "Crime CC"

*60-91
ivreghdfe lnpop_cc (vc_pc = inter_badtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_cl_magnitude, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop CC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Year, 60-91, Estimation, IV, F, `ff') replace 
*75-91
ivreghdfe lnpop_cc (vc_pc = inter_badtetra) if year>=1975, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_cl_magnitude, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop CC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Year, 75-91, Estimation, IV, F, `ff')  
*75-91, all crimes
ivreghdfe lnpop_cc (crime_pc = inter_badtetra) if year>=1975, abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 crime_pc using table_cl_magnitude, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop CC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year, YES, Year, 75-91, Estimation, IV, F, `ff')  



///////////////////////////////////////////////////////////////////////////////////////////
///TABLE A.14
///////////////////////////////////////////////////////////////////////////////////////////
use "finaldata\final_cc_data.dta", clear

drop if fipsplace_00==.
xtset fipsplace_00 year

drop if year>1991

*Create instrument and outcome
g good_soil = (ph1_plc_wtm_wtm_0_r>=6.8 & ph1_plc_wtm_wtm_0_r<=7.7)
gen inter_goodtetra=good_soil*tetra_corr
gen inter_badtetra=(1-good_soil)*tetra_corr
g perc_cc =  tot_cc_oripop_corr / (tot_ncc_oripop_corr + tot_cc_oripop_corr)
g pop_msa = tot_ncc_oripop_corr + tot_cc_oripop_corr
gen cdivcodeyear=cdivcode*year

*Create standardized variables
gen vc_pc=totnpcc_cc_offenses_vc*1000
g lvc_pc = ln(vc_pc)
g lnpop_cc = ln(tot_cc_oripop_corr)
g lnpop_ncc = ln(tot_ncc_oripop_corr)
gen lnpop_msa=ln(pop_msa)

*Label variables
label var lvc_pc "ln(Violent crime)"
label var inter_goodtetra "Low LB x Lead"
label var inter_badtetra "High LB x Lead"

***OLS
reghdfe perc_cc lvc_pc, abs(fipsplace_00 year cdivcode#year) vce(cluster cdivcode#year)
outreg2 vc_pc using table_el, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Estimation, OLS, F, .)  replace
***First stage
reghdfe lvc_pc inter_goodtetra, abs(fipsplace_00 year cdivcode#year) vce(cluster cdivcode#year)
outreg2 inter_goodtetra using table_el, tex(frag) nocon noni nor2 nonotes label ctitle("Violent crime") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Estimation, OLS, F, .)
***IV
ivreghdfe perc_cc (lvc_pc = inter_goodtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_el, tex(frag) nocon noni nor2 nonotes label ctitle("Sh. Pop CC") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Estimation, IV, F, `ff')  
***IV CC
ivreghdfe lnpop_cc (lvc_pc = inter_goodtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_el, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop CC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Estimation, IV, F, `ff')  
***IV NCC
ivreghdfe lnpop_ncc (lvc_pc = inter_goodtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_el, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop NCC)") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Estimation, IV, F, `ff')  
***IV MSA
ivreghdfe lnpop_msa (lvc_pc = inter_goodtetra), abs(fipsplace_00 year cdivcode#year) cluster(cdivcodeyear)
local ff= string(round(e(cdf),.01) , "%9.2f")
outreg2 vc_pc using table_el, tex(frag) nocon noni nor2 nonotes label ctitle("ln(Pop MSA)") addtext(MSA FE, YES, Year FE, YES, C. div x Year FE, YES, Estimation, IV, F, `ff') 
